-- 退件签收监控签收维度汇总
-- 侯文龙
-- 日期：{{ execution_date  | cst_ds }}
with reback_transfer_waybill_send as (
    select max(end_manage_region_code)             as manage_code                     --管理大区code
         , max(end_manage_region_name)             as manage_name                     --管理大区名字
         , max(end_agent_code)                     as agent_code                      --代理区code
         , max(end_agent_name)                     as agent_name                      --代理区名字
         , max(end_franchisee_code)                as franchisee_code                 --加盟商code
         , max(end_franchisee_name)                as franchisee_name                 --加盟商名字
         , final_sign_network_code                  as delivery_network_code           --派件网点code
         , max(final_sign_network_name)             as delivery_network_name           --派件网点名字
         , order_source_name                                                          --平台名字
         , max(order_source_code)                  as order_source_code               --平台code
         , 0                                       as back_sign_cnt                   --应签收量
         , 0                                       as before_sign_cnt                 --22点前签收量
         , 0                                       as after_sign_cnt                  --22点后延误签收
         , 0                                       as zypt_cnt                        --22点到24点签收量
         , 0                                       as no_sign_cnt                     --未签收量
         , 0                                       as ypy_cnt                         --一班应签收量
         , 0                                       as ypz_cnt                         --一班准点签收的量
         , 0                                       as epy_cnt                         --二班应签收的量
         , 0                                       as epz_cnt                         --二班准点签收的量
         , 0                                       as spy_cnt                         --三班应签收的量
         , 0                                       as spz_cnt                         -- 三班准点签收的量
         -- ,type1 as business_type     --类型 1 发件 2 签收
         , dt                                      as date_time                       --业务日期
         , 0                                       as need_sign_cnt_1030              -- 10：30应签收
         , 0                                       as aging_sign_cnt_1030             -- 10：30准点签收
         , 0                                       as need_sign_cnt_1130              -- 11：30应签收
         , 0                                       as aging_sign_cnt_1130             -- 11：30准点签收
         , 0                                       as need_sign_cnt_1400              -- 14：00应签收
         , 0                                       as aging_sign_cnt_1400             -- 14：00准点签收
         , 0                                       as need_sign_cnt_1800              -- 18：00应签收
         , 0                                       as aging_sign_cnt_1800             -- 18：00准点签收
         , 0                                       as need_sign_cnt_2200              -- 22:00应签收量
         , 0                                       as aging_sign_cnt_2200             -- 22:00准点签收量
         -- 新增
         , sum(is_departure_on_time)               as departure_on_time_sum           -- 准点接件量
         , sum(if(is_departure_on_time = 0, 1, 0)) as departure_later_time_sum        -- 晚点接件量
         , sum(is_arrival_on_time)                 as arrival_on_time_sum             --准点到车量
         , sum(if(is_arrival_on_time = 0, 1, 0))      arrival_later_time_sum          --晚点到车量
        -- , 0                                       as need_deliver_sum                --应派件总量
         , 0                                       as deliver_on_time_sum             -- 准点派件量
         , 0                                       as deliver_township_on_time_sum    --乡镇准点派件量
         , 0                                       as deliver_later_time_sum          --不准点派件量
         , 0                                       as deliver_township_later_time_sum --乡镇不准点派件量
         ,count(1)  as send_need_sign_sum          --发件应签收
         ,0 as no_appraisal_sum  --不考核汇总
         , dt                                                                         --分区
    from jms_dm.dm_reback_transfer_waybill_type_detail_dt
    where dt > date_add('{{ execution_date | cst_ds }}', -30)
      and type1 = 1
      and dt <= '{{ execution_date | cst_ds }}'
    group by dt, final_sign_network_code, order_source_name
),
     reback_transfer_waybill_sign as (
         select max(end_manage_region_code)                                                                   as manage_code                     --管理大区code
              , max(end_manage_region_name)                                                                   as manage_name                     --管理大区名字
              , max(end_agent_code)                                                                           as agent_code                      --代理区code
              , max(end_agent_name)                                                                           as agent_name                      --代理区名字
              , max(end_franchisee_code)                                                                      as franchisee_code                 --加盟商code
              , max(end_franchisee_name)                                                                      as franchisee_name                 --加盟商名字
              , final_sign_network_code                                                                        as delivery_network_code           --派件网点code
              , max(final_sign_network_name)                                                                   as delivery_network_name           --派件网点名字
              , order_source_name                                                                                                                --平台名字
              , max(order_source_code)                                                                        as order_source_code               --平台code
              , count(1)                                                                                      as back_sign_cnt                   --应签收量
              , sum(is_aging_sign_22)                                                                         as before_sign_cnt                 --22点前签收量
              , sum(after_aging_sign_22)                                                                      as after_sign_cnt                  --22点后延误签收
              , sum(is_aging_sign_24)                                                                         as zypt_cnt                        --22点到24点签收量
              , sum(if(aging_sign_time is null, 1, 0))                                                        as no_sign_cnt                     --未签收量
              , sum(if(deliver_shift = 1, 1, 0))                                                              as ypy_cnt                         --一班应签收量
              , sum(
                 if(deliver_shift = 1 and aging_sign_time <= final_plan_sign_time, 1, 0))                     as ypz_cnt                         --一班准点签收的量
              , sum(if(deliver_shift = 2, 1, 0))                                                              as epy_cnt                         --二班应签收的量
              , sum(
                 if(deliver_shift = 2 and aging_sign_time <= final_plan_sign_time, 1, 0))                     as epz_cnt                         --二班准点签收的量
              , sum(if(deliver_shift = 3, 1, 0))                                                              as spy_cnt                         --三班应签收的量
              , sum(
                 if(deliver_shift = 3 and aging_sign_time <= final_plan_sign_time, 1, 0))                     as spz_cnt                         -- 三班准点签收的量
              -- ,type1 as business_type     --类型 1 发件 2 签收
              , dt                                                                                            as date_time                       --业务日期
              , sum(if(deadline_signing_time = '10:30:00', 1, 0))                                             as need_sign_cnt_1030              -- 10：30应签收
              , sum(if(deadline_signing_time = '10:30:00' and aging_sign_time <= final_plan_sign_time, 1,
                       0))                                                                                    as aging_sign_cnt_1030             -- 10：30准点签收
              , sum(if(deadline_signing_time = '11:30:00', 1, 0))                                             as need_sign_cnt_1130              -- 11：30应签收
              , sum(if(deadline_signing_time = '11:30:00' and aging_sign_time <= final_plan_sign_time, 1,
                       0))                                                                                    as aging_sign_cnt_1130             -- 11：30准点签收
              , sum(if(deadline_signing_time = '14:00:00', 1, 0))                                             as need_sign_cnt_1400              -- 14：00应签收
              , sum(if(deadline_signing_time = '14:00:00' and aging_sign_time <= final_plan_sign_time, 1,
                       0))                                                                                    as aging_sign_cnt_1400             -- 14：00准点签收
              , sum(if(deadline_signing_time = '18:00:00', 1, 0))                                             as need_sign_cnt_1800              -- 18：00应签收
              , sum(if(deadline_signing_time = '18:00:00' and aging_sign_time <= final_plan_sign_time, 1,
                       0))                                                                                    as aging_sign_cnt_1800             -- 18：00准点签收
              , sum(if(deadline_signing_time = '22:00:00', 1, 0))                                             as need_sign_cnt_2200              -- 22:00应签收量
              , sum(if(deadline_signing_time = '22:00:00' and aging_sign_time <= final_plan_sign_time, 1,
                       0))                                                                                    as aging_sign_cnt_2200             -- 22:00准点签收量
              -- 新增
              , 0                                                                                             as departure_on_time_sum           -- 准点接件量
              , 0                                                                                             as departure_later_time_sum        -- 晚点接件量
              , 0                                                                                             as arrival_on_time_sum             --准点到车量
              , 0                                                                                             as arrival_later_time_sum          --晚点到车量
              -- 出仓
              --, count(1) as need_deliver_sum  --应派件总量
              , sum(is_deliver_on_time)                                                                       as deliver_on_time_sum             -- 准点派件量
              , sum(is_deliver_township_on_time)                                                              as deliver_shiptown_on_time_sum    --乡镇准点派件量
              , sum(if(is_deliver_on_time = 0, 1, 0))                                                         as deliver_later_time_sum          --不准点派件量
              , sum(if(is_deliver_township_on_time = 0, 1, 0))                                                as deliver_township_later_time_sum --乡镇不准点派件量
              ,0  as send_need_sign_sum          --发件应签收
              ,sum(if(sign_type=3,1,0)) as no_appraisal_sum  --不考核汇总
              , dt                                                                                                                               --分区
         from jms_dm.dm_reback_transfer_waybill_type_detail_dt
         where dt > date_add('{{ execution_date | cst_ds }}', -30)
           and dt <= '{{ execution_date | cst_ds }}'
           and type1 = 2
         group by dt, final_sign_network_code, order_source_name
     )
insert overwrite table jms_dm.dm_reback_transfer_plan_sign_sum_dt partition (dt)
select max(manage_code)                                                                   as manage_code                     --管理大区code
     , max(manage_name)                                                                   as manage_name                     --管理大区名字
     , max(agent_code)                                                                           as agent_code                      --代理区code
     , max(agent_name)                                                                           as agent_name                      --代理区名字
     , max(franchisee_code)                                                                      as franchisee_code                 --加盟商code
     , max(franchisee_name)                                                                      as franchisee_name                 --加盟商名字
     , delivery_network_code                                                                        as delivery_network_code           --派件网点code
     , max(delivery_network_name)                                                                   as delivery_network_name           --派件网点名字
     , order_source_name                                                                                                                --平台名字
     , max(order_source_code)                                                                        as order_source_code               --平台code
     , sum(back_sign_cnt)                                                                                    as back_sign_cnt                   --应签收量
     , sum(before_sign_cnt)                                                                         as before_sign_cnt                 --22点前签收量
     , sum(after_sign_cnt)                                                                      as after_sign_cnt                  --22点后延误签收
     , sum(zypt_cnt)                                                                         as zypt_cnt                        --22点到24点签收量
     , sum(no_sign_cnt)                                                        as no_sign_cnt                     --未签收量
     , sum(ypy_cnt)                                                              as ypy_cnt                         --一班应签收量
     , sum(ypz_cnt)                     as ypz_cnt                         --一班准点签收的量
     , sum(epy_cnt)                                                              as epy_cnt                         --二班应签收的量
     , sum(epz_cnt)                     as epz_cnt                         --二班准点签收的量
     , sum(spy_cnt)                                                              as spy_cnt                         --三班应签收的量
     , sum(spz_cnt)                     as spz_cnt                         -- 三班准点签收的量
     , dt                                                                   as date_time                       --业务日期
     , sum(need_sign_cnt_1030)                                             as need_sign_cnt_1030              -- 10：30应签收
     , sum(aging_sign_cnt_1030)                                            as aging_sign_cnt_1030             -- 10：30准点签收
     , sum(need_sign_cnt_1130)                                             as need_sign_cnt_1130              -- 11：30应签收
     , sum(aging_sign_cnt_1130)                                             as aging_sign_cnt_1130             -- 11：30准点签收
     , sum(need_sign_cnt_1400)                                             as need_sign_cnt_1400              -- 14：00应签收
     , sum(aging_sign_cnt_1400)                                             as aging_sign_cnt_1400             -- 14：00准点签收
     , sum(need_sign_cnt_1800)                                             as need_sign_cnt_1800              -- 18：00应签收
     , sum(aging_sign_cnt_1800)                                             as aging_sign_cnt_1800             -- 18：00准点签收
     , sum(need_sign_cnt_2200)                                             as need_sign_cnt_2200              -- 22:00应签收量
     , sum(aging_sign_cnt_2200)                                            as aging_sign_cnt_2200             -- 22:00准点签收量
     -- 新增
     , sum(departure_on_time_sum)                                           as departure_on_time_sum           -- 准点接件量
     , sum(departure_later_time_sum)                                         as departure_later_time_sum        -- 晚点接件量
     , sum(arrival_on_time_sum)                                                as arrival_on_time_sum             --准点到车量
     , sum(arrival_later_time_sum)                                             as  arrival_later_time_sum          --晚点到车量
     , sum(deliver_on_time_sum)                                                                       as deliver_on_time_sum             -- 准点派件量
     , sum(deliver_township_on_time_sum)                                                              as deliver_township_on_time_sum    --乡镇准点派件量
     , sum(deliver_later_time_sum)                                                         as deliver_later_time_sum          --不准点派件量
     , sum(deliver_township_later_time_sum)                                                as deliver_township_later_time_sum --乡镇不准点派件量
     ,sum(send_need_sign_sum) as send_need_sign_sum  --发件应签收总量
     ,sum(no_appraisal_sum) as no_appraisal_sum  --不考核汇总
     , dt            --分区

from (
         select *
         from reback_transfer_waybill_send
         union all
         select *
         from reback_transfer_waybill_sign
     ) tmp
group by dt, delivery_network_code, order_source_name
 distribute by dt, abs(hash(delivery_network_code)) % 1 ;


